package com.casic.modules.jy.service.impl;

import com.casic.common.exception.RRException;
import com.casic.modules.jy.entity.JyRklsEntity;
import com.casic.modules.jy.service.JyRklsService;
import com.casic.modules.sys.entity.SysUserEntity;
import com.casic.modules.sys.service.SysUserService;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.casic.common.base.service.impl.BaseServiceImpl;

import com.casic.modules.jy.dao.JyXsjbxxDao;
import com.casic.modules.jy.entity.JyXsjbxxEntity;
import com.casic.modules.jy.service.JyXsjbxxService;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


@Service("jyXsjbxxService")
public class JyXsjbxxServiceImpl extends BaseServiceImpl<JyXsjbxxDao,JyXsjbxxEntity>  implements JyXsjbxxService {
    @Autowired
    private JyRklsService jyRklsService;
    @Autowired
    private SysUserService sysUserService;
    @Autowired
    public void setDao(JyXsjbxxDao jyXsjbxxDao) {
        this.dao=jyXsjbxxDao;
    }

    @Override
    public List<JyXsjbxxEntity> selectByXh(String xh) {
        return this.dao.selectByXh(xh);
    }



    public boolean addFromExcel(String fileName, MultipartFile file) throws Exception {

        boolean notNull = false;
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new RRException("上传文件格式不正确");
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(0);
        if(sheet!=null){
            notNull = true;
        }
        JyXsjbxxEntity jyXsjbxxEntity;
        SysUserEntity sysUserEntity;
        StringBuffer warnMsg = new StringBuffer("");
        List<JyXsjbxxEntity> xsList = new ArrayList<JyXsjbxxEntity>();
        List<SysUserEntity> userList = new ArrayList<SysUserEntity>();
        for (int r = 1; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);
            if (row == null){
                continue;
            }

            jyXsjbxxEntity = new JyXsjbxxEntity();
            sysUserEntity = new SysUserEntity();


            String name = checkCell(row, 0);

            if(name == null || name.isEmpty()){
                warnMsg.append("导入失败(第"+(r+1)+"行,姓名未填写)<br>");
                continue;
            }

            String xh = checkCell(row, 1);
            if(xh==null || xh.isEmpty()){
                warnMsg.append("导入失败(第"+(r+1)+"行,学号未填写)<br>");
                continue;
            }
            List<JyRklsEntity> lsList = jyRklsService.selectByGh(xh);
            if(lsList != null && lsList.size() > 0){
                warnMsg.append("导入失败(第"+(r+1)+"行,学号在教师信息表中已存在！<br>");
                continue;
            }
            List<JyXsjbxxEntity> xhList = this.dao.selectByXh(xh);
            if(xhList != null && xhList.size() > 0){
                warnMsg.append("导入失败(第"+(r+1)+"行,学号在学生信息表中已存在！<br>");
                continue;
            }
            SysUserEntity user = sysUserService.queryByUserName(xh);
            if(user != null){
                warnMsg.append("导入失败(第"+(r+1)+"行,学号在登录用户信息表中已存在！<br>");
                continue;
            }
            String xb = checkCell(row, 2);
            String sfzh = checkCell(row, 3);
            String hjszd = checkCell(row, 4);
            String hjszdxz = checkCell(row, 5);
            String xjzd = checkCell(row, 6);
            String qqh = checkCell(row, 7);
            String wxh = checkCell(row, 8);
            String bz = checkCell(row, 9);
            String bm = checkCell(row, 10);
            String yx = checkCell(row, 11);
            String sjh = checkCell(row, 12);

            jyXsjbxxEntity.setId(createId());
            jyXsjbxxEntity.setXb("男".equals(xb)?"0":"1");
            jyXsjbxxEntity.setBz(bz);
            jyXsjbxxEntity.setXh(xh);
            jyXsjbxxEntity.setSfzh(sfzh);
            jyXsjbxxEntity.setHjszd(hjszd);
            jyXsjbxxEntity.setHjszdxz(hjszdxz);
            jyXsjbxxEntity.setXzdz(xjzd);
            jyXsjbxxEntity.setQqh(qqh);
            jyXsjbxxEntity.setWxh(wxh);
            jyXsjbxxEntity.setCreateDate(new Date());
            jyXsjbxxEntity.setCreateUserId("1");
            jyXsjbxxEntity.setDeleteState("0");
            xsList.add(jyXsjbxxEntity);

            sysUserEntity.setUserId(createId());
            sysUserEntity.setUsername(xh);
            sysUserEntity.setPassword("123456");
            sysUserEntity.setNickName(name);
            sysUserEntity.setEmail(yx);
            sysUserEntity.setMobile(sjh);
            sysUserEntity.setDeptId(bm);
            List<String> roleList = new ArrayList<String>();
            roleList.add("e7a7221f541a4ed9b37d888504ad78e4");
            List<String> deptList = new ArrayList<String>();
            deptList.add(bm);
            sysUserEntity.setStatus(1);
            sysUserEntity.setRoleIdList(roleList);
            sysUserEntity.setDeptIdList(deptList);
            userList.add(sysUserEntity);
        }
        if(warnMsg.toString().length() > 0){
            throw new RRException(warnMsg.toString());
        }
        for (JyXsjbxxEntity xs : xsList) {
            this.dao.save(xs);
        }
        for(SysUserEntity user : userList){
            sysUserService.save(user);
        }
        return notNull;
    }

    private String checkCell(Row row, int cellNumber){
        String returnStr = null;
        Cell cell = row.getCell(cellNumber);
        if(cell != null){
            row.getCell(cellNumber).setCellType(Cell.CELL_TYPE_STRING);
            returnStr = row.getCell(cellNumber).getStringCellValue();
        }

        return returnStr;
    }
}
